package com.example.demo.repository.primary;

import com.example.demo.entity.primary.JobQueueEntity;
import com.example.demo.repository.base.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Optional;

/**
 * Created by dongjunpeng on 2017/10/16.
 */
@Repository
public interface JobQueuesRepository extends BaseRepository<JobQueueEntity, Integer> {

    JobQueueEntity findByJobQueueCode(String jobQueueCode);


    /**
     * @author ZSP
     * @description 获取集装箱前后位置
     */
    @Query(value = "SELECT REPLACE(CONVERT(( " +
            "       CASE " +
            "           WHEN Json_extract(jq.containers,  " +
            "               '$[0].container_no') = vc.container_number " +
            "               THEN Json_extract(jq.containers, '$[0].position')  " +
            "           ELSE  " +
            "               Json_extract(jq.containers, '$[1].position') end ), CHAR), '\"',  " +
            "       '') AS  position  " +
            "FROM   job_queue jq  " +
            "       LEFT JOIN vessel_container vc  " +
            "              ON jq.job_queue_code = vc.job_queue_code  " +
            "WHERE  vc.vessel_voyage_number = ?1  " +
            "       AND vc.container_number = ?2  " +
            "       AND vc.job_type = ?3  ", nativeQuery = true)
    Optional<String> getPositionByVesselContainerRecords(String vesselVoyageNumber, String containerNumber, String jobType);


}
